IF OBJECT_ID('dbo.vwAgreementBudgets') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwAgreementBudgets
    IF OBJECT_ID('dbo.vwAgreementBudgets') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwAgreementBudgets >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwAgreementBudgets >>>'
END
GO
-- =======================================================================
-- Object Name: (VIEW) dbo.vwAgreementBudgets
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 8/20/2007
--
-- Description: View to get Budget data, including Project Level Budget info.
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 8/20/07     KC        Created
-- 8/22/07     KC        Finalized, 
-- 9/04/07     KC        Added FundNumber
-- 9/05/07     KC        Changed to use Budget Fact Table
-- 9/6/07      KC        Differences in Budget dates make use of 
--                       DATEPART(YY,BudgetStartDate) necessary
-- 10/4/07     KC        New logic to parse Amounts from BudgetAmounts
--                       by FinCategoryId
-- 10/30/07    KC        Made JOIN to dbo.AgrFund an OUTER JOIN
-- 11/3/07     KC        Change for Pending Budgets. Isnull on FundId
-- 12/18/07    KC        Adjustment to handle negative BudgetAmounts in
--                       top part of View.
-- 12/27/07    KC        Added MasterPropStatus check, and Year and IsAdjustment
-- 02/21/08    KC        Moved BudgetYear calculation to outer-most query.
--                       Also put in fix to pull actual BudgetDates for chosen
--                       Status...instead of Min/Max which could have changed them.
-- 04/03/09	   KC		Removed FundId from Inner-Most joins. Was dropping
--					    periods where FundID changed from Pending to Awarded
--					    May be an issue when FundId changes within a Year!
--
-- =======================================================================
CREATE VIEW dbo.vwAgreementBudgets
AS
     -- FOURTH QUERY: We have all the data we need at this point, but we have two records by AgreementId,Fund, BudgetYear,
     -- BudgetDates, BudgetViewType (Awarded, Committed, Pending), IndirectCostBasis, and IndirectCostRate.
     -- This step adds the two records together (number plus 0 seeded below in THIRD QUERY) and gives us a single record.
     SELECT    AgreementId,
               FundId,
               FundNumber,
               DATEPART(YY,BudgetStartDate) AS BudgetYear,
               SequenceYear,
               IsAdjustment,
               BudgetStartDate,
               BudgetEndDate,
               BudgetViewType,
               SUM (BudgetDirectCost) AS BudgetDirectCost,
               SUM (BudgetIndirectCost) AS BudgetIndirectCost,
               SUM (BudgetDirectCost) + SUM(BudgetIndirectCost) AS BudgetTotalAmount,
               IndirectCostBasis,
               IndirectCostRate
     FROM (
          -- THIRD QUERY: Transpose the DIRECT and INDIRECT records into separate columns, and SUM BY Agreement,
          -- Fund, BudgetYear. Also gets the BudgetStart and BudgetEnd Dates by these criteria.
          -- This results in Two Records for each GROUP BY Criteria, because of the CASE statement and 0 seeding.
          -- so the Query Above Combines the Records and gets rid of the Zero.
          -- NEW! DO NOT use Adjustments to figure out the BudgetDates, We need to add them, but ignore them
          -- for the Date calculation!!
          SELECT    B.AgreementId,
                    B.FundId,
                    B.FundNumber,
                    B.Year AS SequenceYear,
                    B.IsAdjustment,
                    MIN(B.BudgetStartDate) AS BudgetStartDate,
                    MAX(B.BudgetEndDate) AS BudgetEndDate,
                    B.BudgetViewType,
                    CASE WHEN B.BudgetAmountType = 'DIRECT'
                         THEN SUM(B.BudgetAmount) 
                         ELSE 0.00
                    END AS BudgetDirectCost,
                    CASE WHEN B.BudgetAmountType = 'INDIRECT'
                         THEN SUM(B.BudgetAmount) 
                         ELSE 0.00
                    END AS BudgetIndirectCost,
                    B.IndirectCostBasis,
                    MAX(B.IndirectCostRate) AS IndirectCostRate
          FROM      (   
          -- SECOND QUERY: Sums the Amounts by Agreement, Fund, BudgetDates and BudgetViewType (determined in the FIRST INNER QUERY)
          -- It also tags the records as INDIRECT or DIRECT, based on the FinCategoryId, and Calcs the BudgetYear for each Record.
          -- Next, we need to transpose the DIRECT and INDIRECT Amounts (Summed) into separate columns (above) 
          SELECT    BUDG.AgreementId,
                    BUDG.FundId,
                    ISNULL(FU.FundNumber,'') AS FundNumber,
                    BP.Year,
                    BUDG.IsAdjustment,
                    BUDG.BudgetStartDate,
                    BUDG.BudgetEndDate,
                    CASE BUDG.BudgetViewTypeId 
                         WHEN 3 THEN 'Awarded' 
                         WHEN 6 THEN 'Committed' 
                         WHEN 1 THEN 'Pending' 
                    END AS BudgetViewType,
                    CASE WHEN FA.FinCategoryId = 9
                        THEN 'INDIRECT'
                        ELSE 'DIRECT'
                    END AS BudgetAmountType,
                    SUM(BUDG.BudgetAmount) AS BudgetAmount,
                    BUDG.IndirectCostBasis AS IndirectCostBasis,
                    BUDG.IndirectCostRate AS IndirectCostRate
          FROM dbo.AgrBudget_Fct BUDG 
               INNER JOIN dbo.FinAccount FA ON BUDG.AccountId =  FA.Id
               INNER JOIN (
                    -- REFERRED TO AS FIRST INNER QUERY! 
                    -- This gets the "BEST" BudgetType for each BudgetStartYear in this order, Awarded:Committed:Pending
                    -- Join on this Search, and get the amounts and dates by AgreementId, FundId, BudgetStartYear
                    -- NEW! Use this query to get BudgetStart and End Dates while ignoring Adjustments
                    SELECT    B.AgreementId,
                              B.Year,
                              CASE WHEN A.MasterProposalStatus = 'Pending'
                                  THEN 'Pending'
                                  ELSE MIN(CASE B.BudgetViewTypeId 
                                       WHEN 3 THEN 'Awarded'
                                       WHEN 6 THEN 'Committed'
                                       WHEN 1 THEN 'Pending'
                                  END )
                              END AS BudgetViewType 
                    FROM      dbo.AgrBudget_Fct B JOIN dbo.AgrAgreement_Info A ON B.AgreementId = A.AgreementId
                    WHERE     B.IsAdjustment = 0 
                    GROUP BY  B.AgreementId, 
                              A.MasterProposalStatus,
                              B.Year) BP ON BUDG.AgreementID = BP.AgreementID 
                                        AND BUDG.Year = BP.Year 
                                        AND CASE BUDG.BudgetViewTypeId 
                                             WHEN 3 THEN 'Awarded' 
                                             WHEN 6 THEN 'Committed' 
                                             WHEN 1 THEN 'Pending' 
                                        END = BP.BudgetViewType 
               LEFT OUTER JOIN dbo.AgrFund FU ON BUDG.FundId = FU.Id
               GROUP BY  BUDG.AgreementID, 
                         BUDG.FundId,
                         FU.FundNumber,
                         BUDG.BudgetStartDate, 
                         BUDG.BudgetEndDate, 
                         BUDG.BudgetViewTypeId,
                         FA.FinCategoryId,
                         BUDG.IndirectCostBasis,
                         BUDG.IndirectCostRate,
                         BP.Year,
                         BUDG.IsAdjustment) B
               GROUP BY  B.AgreementId,
                         B.FundId,
                         B.FundNumber,
                         B.BudgetViewType,
                         B.BudgetAmountType,
                         B.IndirectCostBasis,
                         B.Year,
                         B.IsAdjustment
          ) BB
          GROUP BY  AgreementId,
                    FundId,
                    FundNumber,
                    BudgetStartDate,
                    BudgetEndDate,
                    BudgetViewType,
                    IndirectCostBasis,
                    IndirectCostRate,
                    SequenceYear,
                    IsAdjustment
                    
GO

IF OBJECT_ID('dbo.vwAgreementBudgets') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwAgreementBudgets >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwAgreementBudgets >>>'
GO
